package com.kele.util;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Version 2019
 * @Author:kele
 * @Date:2021/4/16
 * @Content:
 * @Description：导出excel工具类
 */
public class CreatExcelUtils {

    public static int tableToExcel(String tableName, String path){
        //记录开始时间
        Long begin=new Date().getTime();

        //获取连接对象
        Connection connection = CollectionUtils.getConnection();
        //定义sql
        String sql="select * from "+tableName;
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            //获得字段名
            ResultSetMetaData data = rs.getMetaData();

            //获取字段名
            List<String> nameList=new ArrayList<>();
            for (int i = 0; i <data.getColumnCount() ; i++) {
                nameList.add(data.getColumnName(i+1));
            }
            //创建excel对象
            Workbook workbook=new XSSFWorkbook();
            //创建sheet页
            Sheet sheet = workbook.createSheet();

            //创建行对象
            Row row = sheet.createRow(0);
            //创建列对象
            for (int i = 0; i <nameList.size() ; i++) {
                Cell cell=row.createCell(i);
                //给cell赋值
                cell.setCellValue(nameList.get(i));
            }
            int index = 1;
            while (rs.next()){
                Row row2 = sheet.createRow(index);
                for (int i = 0; i < nameList.size(); i++){
                    Cell cell = row2.createCell(i);
                    cell.setCellValue(rs.getObject(i+1).toString());
                }
                index++;
            }
            //将封装好的workbook写入excel
            File file=new File(path);
            if (!file.exists()){
                file.mkdirs();
            }
            workbook.write(new FileOutputStream(file+"\\"+tableName+" .xlsx"));
            workbook.close();

            //结束时间
            Long end=new Date().getTime();
            System.out.println("导出数据消耗时间 : " + (end - begin) / 1000 + " s");
            System.out.println("导出成功，请到" + path + "查看");


        } catch (Exception e) {
            e.printStackTrace();
        }

        return 1;
    }
}
